package com.apexedu.portal.util;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;

import com.apexedu.biz.Const;
import com.apexedu.framework.jdbc.DataSourceEntity;
import com.apexedu.framework.jdbc.DataSourceManager;
import com.apexedu.framework.jdbc.handler.ResultSetHandler;
import com.apexedu.framework.util.DBUtil;
import com.apexedu.framework.util.Pager;
import com.apexedu.portal.entity.Notice;
import com.apexedu.portal.entity.TDb;

/**
 * 多数据源的DBUtil
 * Created by ly on 2014/8/19.
 */
public class DBMUtil {

	private static final Log LOG = LogFactory.getLog(DBMUtil.class);

	private String dbName = "liferay";

	private JdbcTemplate jdbcTemplate;

	/**
	 * 执行更新语句，可以是insert、update或者delete语句
	 *
	 * @param sql SQL预编译语句（可带?的那种）
	 * @param in_params 参数
	 * @return 受影响的记录行数
	 */
	public int executeSQL(String sql, Object... in_params) throws Exception {
		return jdbcTemplate.update(sql, in_params);
	}

	/**
	 * 查询对象集合
	 *
	 * @param sql ---
	 * @param beanClass 对象class
	 * @param params 查询参数
	 * @return 对象集合
	 * @throws Exception
	 */
	public <T> List<T> queryAllBeanList(String sql, Class<T> beanClass, Object... params) throws Exception {
		return queryPageBeanList(sql, null, beanClass, params);
	}

	/**
	 * 查询对象集合（分页）
	 *
	 * @param sql --
	 * @param pager 分页对象
	 * @param beanClass 对象class
	 * @param params 查询参数
	 * @return 对象集合
	 * @throws Exception
	 */
	public <T> List<T> queryPageBeanList(String sql, Pager pager, Class<T> beanClass, Object... params) throws Exception {
		if (pager != null) {
			sql = convert2OraclePagedSQL(pager, sql);
		}
		return jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(beanClass), params);
	}

	/**
	 * 返回ORACLE数据库分页语句
	 *
	 * @param pager 分页对象
	 * @param sql 原始的查询语句
	 * @return ORACLE数据库分页语句
	 */
	private static String convert2OraclePagedSQL(Pager pager, String sql) {
		int startInt = (pager.getCurrentPageno() - 1) * pager.getEachPageRows();
		int endInt = pager.getCurrentPageno() * pager.getEachPageRows();
		sql = "SELECT * FROM (SELECT ROW_.*,ROWNUM RN FROM ( " + sql + ") ROW_ ) WHERE RN > " + startInt + " AND RN <= " + endInt;
		return sql;
	}

	/**
	 * 把查询结果封装到list<Map>之中，配合页面EL等表达式，可以直接list[i].fieldName取值。
	 *
	 * @param pager 分页对象
	 * @param sql
	 * @param params
	 * @return
	 * @since 2.1
	 */
	public List<Map<String, Object>> queryForList(Pager pager, String sql, Object... params) throws Exception {
		if (pager != null) {
			sql = convert2OraclePagedSQL(pager, sql);
		}
		return jdbcTemplate.queryForList(sql, params);
	}

	/**
	 * 查询后返回一个对象 注意：通过对象属性名称填充对象。推荐采用此方法替代早前的构造函数创建对象方法。
	 *
	 * @param beanClass 结果对象
	 * @param sql --
	 * @param params 参数值
	 * @return 单个结果对象，当查询的对象不存的时候，返回null
	 * @throws SQLException
	 */
	public <T> T queryBean(String sql, Class<T> beanClass, Object... params) throws Exception {
		return queryAllBeanList(sql, beanClass, params).get(0);
	}

	/**
	 * 查询一个字段的值。某行里的某列的值。
	 *
	 * @param sql --
	 * @param params 参数值
	 * @return 某行里的某列的值。单个结果对象。
	 * @throws SQLException
	 */
	public Object queryFieldValue(String sql, Object... params) throws Exception {
		try {
			return jdbcTemplate.queryForObject(sql, Object.class, params);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 查询记录总数
	 *
	 * @param sql select count(*) from ...语句，如果语句是 from...格式，会自动加上select count(*)部分。
	 * @return 统计出来的记录数
	 */
	public int count(String sql, Object... params) throws Exception {

		if (StringUtils.isBlank(sql)) {
			throw new RuntimeException("期望是select count(*) from ...查询语句，实际传入了空字符串！");
		}

		if (sql.trim().toUpperCase().startsWith("FROM")) {
			sql = "SELECT COUNT(*) " + sql;
		}
		if (!sql.toUpperCase().contains("COUNT(")) {
			throw new RuntimeException(sql + " 不是有效的select count(*) from ...查询语句！");
		}
		int rowsCount = 0;
		Object val = queryFieldValue(sql, params);
		String s_val = String.valueOf(val);
		if (s_val != null) {
			rowsCount = Integer.parseInt(s_val);
		}
		return rowsCount;
	}

	public List<Notice> getNotices(String sql) {
		List<Notice> list = jdbcTemplate.query(sql, new Object[] {}, new RowMapper<Notice>() {
			@Override
			public Notice mapRow(ResultSet rs, int i) throws SQLException {
				Notice notice = new Notice();
				notice.setId(rs.getString("id"));
				notice.setTitle(rs.getString("name"));
				notice.setDate(rs.getString("createdate"));
				notice.setFbr(rs.getString("fbr"));
				notice.setDjs(rs.getString("djs"));
				notice.setXxnr(rs.getString("xxnr"));
				notice.setAttachment(rs.getString("attachment"));
				notice.setPicture1(rs.getString("picture1"));
				notice.setPicture2(rs.getString("picture2"));
				java.sql.Blob blob = rs.getBlob("attach");
				notice.setLyid(rs.getString("lyid"));
				String attachName = readFileNameFromBlob(blob);
				if (attachName == null || attachName.equals("")) {
					attachName = rs.getString("name") + ".doc";
				}
				notice.setAttachName(attachName);
				return notice;
			}
		});
		return list;
	}

	public DBMUtil() {
		super();
		initDateSource();
	}

	public DBMUtil(String dbName) {
		super();
		try {
			this.dbName = dbName;
			initDateSource();
		} catch (Exception e) {
			LOG.error("DBMUtil ERROR :" + dbName);
		}
	}

	/**
	 * 获取数据库操作模板
	 */
	private void initDateSource() {
		try {
			DataSource ds = DataSourceManager.getDataSource(dbName);
			this.jdbcTemplate = new JdbcTemplate(ds);
		} catch (Exception e) {
			try {
				System.out.println("获取数据库模板异常");
				TDb db = DBUtil.queryBean("select * from t_db where code = ?", TDb.class, dbName);
				if (db == null) {
					throw new RuntimeException("该数据源未配置：" + dbName);
				}
				DataSourceEntity entity = new DataSourceEntity();
				entity.setDataSourceName(dbName);
				entity.setDriver(db.getDriver());
				entity.setUrl(db.getUrl());
				entity.setUsername(db.getUsername());
				entity.setPassword(db.getPwd());
				entity.setMinPoolSize(3);
				entity.setMaxPoolSize(500);
				DataSourceManager.putNewDataSource(entity);
				DataSource ds = DataSourceManager.getDataSource(dbName);
				this.jdbcTemplate = new JdbcTemplate(ds);
			} catch (Exception e2) {
				e2.printStackTrace();
			}

		}
	}

	/**
	 * 读livebos blob文件名
	 *
	 * @param blob
	 * @return
	 */
	public String readFileNameFromBlob(Blob blob) {
		BufferedInputStream bis = null;
		try {
			if (blob == null) {
				return "";
			}
			bis = new BufferedInputStream(blob.getBinaryStream());
			// 文件写入的缓冲
			byte[] buff = new byte[2048];
			// 一次读取到的字节数
			int bytesRead = 0;
			bytesRead = bis.read(buff, 0, 1);
			/* 由于livebos的特殊性，先从输入流取出文件名长度和文件名 */
			Integer fileNameLen = new Integer(buff[0]);
			//System.out.println(fileNameLen);
			bytesRead = bis.read(buff, 0, fileNameLen);
			String fileName = new String(buff, 0, bytesRead);
			//            System.out.println("fileName: " + fileName);
			return fileName;
		} catch (Exception e) {
			LOG.error(e);
			return null;
		} finally {
			if (bis != null) {
				try {
					bis.close();
				} catch (IOException e) {
					LOG.error(e);
				}
			}
		}
	}

	/**
	 * 查询发文详细信息
	 *
	 * @param recordid
	 * @param sql
	 * @return
	 */
	public List<Notice> readDoc(String recordid, String sql) {
		List<Notice> list = jdbcTemplate.query(sql, new Object[] { recordid }, new RowMapper<Notice>() {
			@Override
			public Notice mapRow(ResultSet rs, int i) throws SQLException {
				Notice notice = new Notice();
				java.sql.Blob blob = rs.getBlob("filebody");
				notice.setFilebody(blob);
				return notice;
			}
		});
		return list;
	}

	/**
	 * 获取数据库类型
	 *
	 * @return
	 * @throws Exception
	 */
	public String getDbType() throws Exception {
		String drivername = this.jdbcTemplate.getDataSource().getConnection().getMetaData().getDriverName();
		if (drivername.indexOf("Oracle") != -1) {
			return Const.DbType.ORACLE;
		} else if (drivername.indexOf("Microsoft") != -1) {
			return Const.DbType.SQLSERVER;
		}
		throw new RuntimeException("数据库类型未获取");
	}

	public static ResultSet queryRowSet(String code, String sql) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		CachedRowSet crs = null;
		TDb db = DBUtil.queryBean("select * from t_db where code = ?", TDb.class, code);
		if (db == null) {
			throw new RuntimeException("该数据源未配置：" + code);
		}
		try {
			crs = ResultSetHandler.getCachedRowSetInstance();
			Class.forName(db.getDriver());
			conn = DriverManager.getConnection(db.getUrl(), db.getUsername(), db.getPwd());
			st = (Statement) conn.createStatement(); //创建用于执行静态sql语句的Statement对象，st属局部变量
			rs = st.executeQuery(sql); //执行sql查询语句，返回查询数据的结果集
			crs.populate(rs);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e1) {
					try {
						if (!conn.isClosed()) {
							conn.close();
						}
					} catch (SQLException e2) {
						e2.printStackTrace();
					}
				}
			}
		}
		return crs;
	}
}
